package edu.gatech.wms.models;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.location.Address;

public class Database {
	private SqlDBHelper			localDBHelper;
	private SQLiteDatabase		localDB;

	private static final Item	nullItem	= new Item( "", null, 0, "", null, 0, null, new LinkedList<String>());
	private static final User	nullUser	= new User("", "", 0, "", "", false);
	
	private static Database		singletonDatabase;
	
	public static Database getDatabaseObject() {
		
		if ( singletonDatabase == null ) {
			
			singletonDatabase = new Database();
			
			Address atlanta = new Address(Locale.getDefault());
			atlanta.setAddressLine(0, "Atlanta, GA.");
			User admin = new User(
					"Administrator",
					"",
					1,
					"5E884898DA28047151D0E56F8DC6292773603D0D6AABBDD62A11EF721D1542D8",
					"admin@gatech.edu", true);
			User jane = new User(
					"Jane",
					"Austin",
					5,
					"5E884898DA28047151D0E56F8DC6292773603D0D6AABBDD62A11EF721D1542D8",
					"jane@yahoo.com", false);
			User james = new User(
					"James",
					"Brown",
					6,
					"5E884898DA28047151D0E56F8DC6292773603D0D6AABBDD62A11EF721D1542D8",
					"brown@aol.com", false);
			
			Item redBike = new Item(
					"Red Bicycle", new FoundItem(), 666,
					"My Rusty Red Bicycle with two flats", null, 5, atlanta,
					Arrays.asList("Other", "Toy"));
			Item bunny = new Item(
					"Soft Bunny Toy", new LostItem(), 667,
					"My Old Lucky Bunny Rabbit", null, 5, atlanta,
					Arrays.asList("Other", "Toy"));
			Item laptop = new Item(
					"Old busted laptop", new LostItem(), 668,
					"Dell XPS Laptop", null, 6, atlanta, Arrays.asList("Toy"));
			Item android = new Item(
					"android galaxy nexus", new LostItem(), 669,
					"My Brand new Cellphone!", null, 6, atlanta,
					Arrays.asList("Electronic"));
			
			singletonDatabase.add(admin);
			singletonDatabase.add(jane);
			singletonDatabase.add(james);
			singletonDatabase.add(redBike);
			singletonDatabase.add(bunny);
			singletonDatabase.add(laptop);
			singletonDatabase.add(android);
		}
		
		return singletonDatabase;
	}
	
	@SuppressLint("UseSparseArrays")
	private Database() {
		localDBHelper = new SqlDBHelper(WMSApplication.getAppContext());
		localDB = localDBHelper.getWritableDatabase();
	}
	
	/**
	 * 
	 * @param userid
	 *            take in unique userid number
	 * @return Return user object for respective ID number
	 */
	public User queryUser(int userid) {
		
		Cursor results = localDB.query(SqlDBHelper.TABLE_USERS, null,
				SqlDBHelper.USER_USERID + "=?", new String[] { "" + userid },
				null, null, null);
		
		if ( results.moveToFirst() ) {
			boolean isAdmin;
			if ( results.getInt(results
					.getColumnIndex(SqlDBHelper.USER_ISADMIN)) > 0 ) {
				isAdmin = true;
			} else {
				isAdmin = false;
			}
			
			User foundUser = new User(
					results.getString(results
							.getColumnIndex(SqlDBHelper.USER_FIRSTNAME)),
					results.getString(results
							.getColumnIndex(SqlDBHelper.USER_LASTNAME)),
					userid, results.getString(results
							.getColumnIndex(SqlDBHelper.USER_PASSWORD)),
					results.getString(results
							.getColumnIndex(SqlDBHelper.USER_CONTACT)), isAdmin);
			
			Cursor resultOwnedList = localDB.query(SqlDBHelper.TABLE_USEROWNED,
					null, SqlDBHelper.USER_USERID + "=?", new String[] { ""
							+ userid }, null, null, null);
			if ( resultOwnedList.moveToFirst() ) {
				do {
					foundUser.addOwnedItem(resultOwnedList
							.getInt(resultOwnedList
									.getColumnIndex(SqlDBHelper.ITEM_ITEMID)));
				} while ( resultOwnedList.moveToNext() );
			}
			
		//	userMemoryDB.put(foundUser.getId(), foundUser);
			
			return foundUser;
		}
		
		return getNullUser();
	}
	
	/**
	 * Query for a user Object by its email address
	 * 
	 * @param email
	 *            Email address of desired User Object
	 * 
	 * @return First User Object with Matching email address
	 */
	public User queryUser(String email) {
		
		Cursor results = localDB.query(SqlDBHelper.TABLE_USERS, null,
				SqlDBHelper.USER_CONTACT + "=?", new String[] { "" + email },
				null, null, null);
		
		if ( results.moveToFirst() ) {
			
			boolean isAdmin;
			if ( results.getInt(results
					.getColumnIndex(SqlDBHelper.USER_ISADMIN)) > 0 ) {
				isAdmin = true;
			} else {
				isAdmin = false;
			}
			
			User foundUser = new User(
					results.getString(results
							.getColumnIndex(SqlDBHelper.USER_FIRSTNAME)),
					results.getString(results
							.getColumnIndex(SqlDBHelper.USER_LASTNAME)),
					results.getInt(results
							.getColumnIndex(SqlDBHelper.USER_USERID)),
					results.getString(results
							.getColumnIndex(SqlDBHelper.USER_PASSWORD)),
					results.getString(results
							.getColumnIndex(SqlDBHelper.USER_CONTACT)), isAdmin);
			
			//userMemoryDB.put(foundUser.getId(), foundUser);
			
			return foundUser;
		}
		
		return getNullUser();
	}
	
	/**
	 * Returns A Set of all Integers for any items that are in DB
	 * 
	 * @return Set of ItemID Integer Values
	 */
	public Collection<Item> queryAllItems() {
		
		Collection<Item> allItems = new LinkedList<Item>();
		
		Cursor results = localDB.query(SqlDBHelper.TABLE_ITEMS, null, null,
				null, null, null, null);
		
		if ( results.moveToFirst() ) {
			
			do {
				
				int idNum = results.getInt(results
						.getColumnIndex(SqlDBHelper.ITEM_ITEMID));
				
				Cursor categoryResults = localDB.query(
						SqlDBHelper.TABLE_ITEMCATEGORIES, null,
						SqlDBHelper.ITEM_ITEMID + "=?", new String[] { ""
								+ idNum }, null, null, null);
				
				List<String> categories = new LinkedList<String>();
				
				if ( categoryResults.moveToFirst() ) {
					do {
						categories
								.add(categoryResults.getString(categoryResults
										.getColumnIndex(SqlDBHelper.ITEMCATEGORY_CATEGORY)));
					} while ( categoryResults.moveToNext() );
				}
				
				int typeInt = results.getInt(results
						.getColumnIndex(SqlDBHelper.ITEM_TYPE));
				
				ItemType itemType = null;
				switch ( typeInt ) {
					case 0:
						itemType = new FoundItem();
						break;
					case 1:
						itemType = new LostItem();
						break;
					case 2:
						itemType = new DonatedItem();
				}
				Cursor resultOwner = localDB.query(SqlDBHelper.TABLE_USEROWNED,
						null, SqlDBHelper.ITEM_ITEMID + "=?", new String[] { ""
								+ idNum }, null, null, null);
				
				resultOwner.moveToFirst();
				
				Item thing = new Item(
						results.getString(results
								.getColumnIndex(SqlDBHelper.ITEM_NAME)),
						itemType, idNum, results.getString(results
								.getColumnIndex(SqlDBHelper.ITEM_DESC)),
						toBitmap(results.getBlob(results
								.getColumnIndex(SqlDBHelper.ITEM_IMAGE))),
						resultOwner.getInt(resultOwner
								.getColumnIndex(SqlDBHelper.USER_USERID)),
						parseItemAddress(results), categories);
				thing.setCreationDate(results.getString(results.getColumnIndex(SqlDBHelper.ITEM_CREATIONDATE)));
				allItems.add(thing);
			} while ( results.moveToNext() );
		}
		return allItems;
		
	}
	
	public Collection<Item> queryItemsByType(int itemTypeInt) {
		
		Collection<Item> allItems = new LinkedList<Item>();
		
		// Cursor results = localDB.query(SqlDBHelper.TABLE_ITEMS,null,
		// null,null,null,null, null);
		Cursor results = localDB.query(SqlDBHelper.TABLE_ITEMS, null,
				SqlDBHelper.ITEM_TYPE + "=?",
				new String[] { "" + itemTypeInt }, null, null, null);
		
		if ( results.moveToFirst() ) {
			
			do {
				
				int idNum = results.getInt(results
						.getColumnIndex(SqlDBHelper.ITEM_ITEMID));
				
				Cursor categoryResults = localDB.query(
						SqlDBHelper.TABLE_ITEMCATEGORIES, null,
						SqlDBHelper.ITEM_ITEMID + "=?", new String[] { ""
								+ idNum }, null, null, null);
				
				List<String> categories = new LinkedList<String>();
				
				if ( categoryResults.moveToFirst() ) {
					do {
						categories
								.add(categoryResults.getString(categoryResults
										.getColumnIndex(SqlDBHelper.ITEMCATEGORY_CATEGORY)));
					} while ( categoryResults.moveToNext() );
				}
				
				int typeInt = results.getInt(results
						.getColumnIndex(SqlDBHelper.ITEM_TYPE));
				
				ItemType itemType = null;
				switch ( typeInt ) {
					case 0:
						itemType = new FoundItem();
						break;
					case 1:
						itemType = new LostItem();
						break;
					case 2:
						itemType = new DonatedItem();
				}
				Cursor resultOwner = localDB.query(SqlDBHelper.TABLE_USEROWNED,
						null, SqlDBHelper.ITEM_ITEMID + "=?", new String[] { ""
								+ idNum }, null, null, null);
				
				resultOwner.moveToFirst();
				
				Item thing = new Item(
						results.getString(results
								.getColumnIndex(SqlDBHelper.ITEM_NAME)),
						itemType, idNum, results.getString(results
								.getColumnIndex(SqlDBHelper.ITEM_DESC)),
						toBitmap(results.getBlob(results
								.getColumnIndex(SqlDBHelper.ITEM_IMAGE))),
						resultOwner.getInt(resultOwner
								.getColumnIndex(SqlDBHelper.USER_USERID)),
						parseItemAddress(results), categories);
				
				thing.setCreationDate(results.getString(results
						.getColumnIndex(SqlDBHelper.ITEM_CREATIONDATE)));
				
				//itemMemoryDB.put(idNum, thing);
				allItems.add(thing);
			} while ( results.moveToNext() );
		}
		return allItems;
		
		// return itemMemoryDB.values();
	}
	
	/**
	 * Query the Database for a Specific item matching the string
	 * 
	 * @param qString
	 * @return A Map of all Item Objects whose properties match the string value
	 */
	@SuppressLint("UseSparseArrays")
	public Map<Integer, Item> queryItem(String qString) {
		Collection<Item> itemMemoryDB = queryAllItems();
		Map<Integer, Item> results = new HashMap<Integer, Item>();
		
		if ( qString == null ) {
			return results;
		}
		
		for ( Item foo : itemMemoryDB ) {
			if ( foo.getDescription().toLowerCase(Locale.getDefault())
					.contains(qString.toLowerCase(Locale.getDefault()))
					|| foo.getName().toLowerCase(Locale.getDefault())
							.contains(qString.toLowerCase(Locale.getDefault()))
					|| foo.getCreationDate().toLowerCase(Locale.getDefault())
							.contains(qString.toLowerCase(Locale.getDefault())) ) {
				results.put(foo.getId(), foo);
			}
			for ( String cat : foo.getCategories() ) {
				if ( cat.toLowerCase(Locale.getDefault()).contains(
						qString.toLowerCase(Locale.getDefault())) ) {
					results.put(foo.getId(), foo);
					break;
				}
			}
		}
		
		// Run some SQL Query Here and add items to list of items matched in
		// memory
		// Also just add the directly to the results linked List
		return results;
	}
	
	/**
	 * A List of All User Objects
	 * 
	 * @return
	 */
	public Collection<User> queryAllUsers() {
		Collection<User> allUsers = new LinkedList<User>();
		
		Cursor results = localDB.query(SqlDBHelper.TABLE_USERS, null, null,
				null, null, null, null);
		results.moveToFirst();
		
		while ( results.moveToNext() ) {
			boolean isAdmin;
			if ( results.getInt(results
					.getColumnIndex(SqlDBHelper.USER_ISADMIN)) > 0 ) {
				isAdmin = true;
			} else {
				isAdmin = false;
			}
			
			User foundUser = new User(
					results.getString(results
							.getColumnIndex(SqlDBHelper.USER_FIRSTNAME)),
					results.getString(results
							.getColumnIndex(SqlDBHelper.USER_LASTNAME)),
					results.getInt(results
							.getColumnIndex(SqlDBHelper.USER_USERID)),
					results.getString(results
							.getColumnIndex(SqlDBHelper.USER_PASSWORD)),
					results.getString(results
							.getColumnIndex(SqlDBHelper.USER_CONTACT)), isAdmin);
			allUsers.add(foundUser);
		}
		
		return allUsers;
	}
	/**
	 * Get A specific Item from DB based on its ID #
	 * 
	 * @param itemid
	 *            Numeric unique Item ID Number
	 * @return Item Object created for Items Data
	 */
	public Item queryItem(int itemid) {
		Cursor results = localDB.query(SqlDBHelper.TABLE_ITEMS, null,
				SqlDBHelper.ITEM_ITEMID + "=?", new String[] { "" + itemid },
				null, null, null);
		if ( results.moveToFirst() ) {
			List<String> categories = new LinkedList<String>();
			int idNum = results.getInt(results
					.getColumnIndex(SqlDBHelper.ITEM_ITEMID));
			Cursor categoryResults = localDB.query(
					SqlDBHelper.TABLE_ITEMCATEGORIES, null,
					SqlDBHelper.ITEM_ITEMID + "=?",
					new String[] { "" + idNum }, null, null, null);
			
			if ( categoryResults.moveToFirst() ) {
				do {
					categories
							.add(categoryResults.getString(categoryResults
									.getColumnIndex(SqlDBHelper.ITEMCATEGORY_CATEGORY)));
				} while ( categoryResults.moveToNext() );
			}
			
			int typeInt = results.getInt(results
					.getColumnIndex(SqlDBHelper.ITEM_TYPE));
			ItemType itemType = null;
			switch ( typeInt ) {
				case 0:
					itemType = new FoundItem();
					break;
				case 1:
					itemType = new LostItem();
					break;
				case 2:
					itemType = new DonatedItem();
					break;
				default:
					itemType = new LostItem();
			}
			
			Cursor resultOwner = localDB.query(SqlDBHelper.TABLE_USEROWNED,
					null, SqlDBHelper.ITEM_ITEMID + "=?", new String[] { ""
							+ itemid }, null, null, null);
			
			int ownerID = 0;
			if ( resultOwner.moveToFirst() ) {
				ownerID = resultOwner.getInt(resultOwner
						.getColumnIndex(SqlDBHelper.USER_USERID));
			}
			
			Item thing = new Item(
					results.getString(results
							.getColumnIndex(SqlDBHelper.ITEM_NAME)), itemType,
					idNum, results.getString(results
							.getColumnIndex(SqlDBHelper.ITEM_DESC)),
					toBitmap(results.getBlob(results
							.getColumnIndex(SqlDBHelper.ITEM_IMAGE))), ownerID,
					parseItemAddress(results), categories);
			
			thing.setCreationDate(results.getString(results
					.getColumnIndex(SqlDBHelper.ITEM_CREATIONDATE)));
			
			//itemMemoryDB.put(idNum, thing);
			return thing;
		}
		
		// return generateItem( results );
		return getNullItem();
	}
	
	/**
	 * Find all Items Based of its Location Value
	 * 
	 * @param loc
	 *            Location of Objects Desired
	 * @return list of Items with specified Location
	 */
	public Collection<Item> queryItem(Address loc) {
		// This will need allot of coding for proximity results
		return null;
	}
	
	/**
	 * Adds the User object to Database
	 * 
	 * @param user
	 *            Adds this user object to the Database (must have unique
	 *            contact field)
	 * @return the users id number
	 */
	
	public int add(User user) {
		
		User foundUser = queryUser(user.getId());
		if ( foundUser != getNullUser() ) {
			return foundUser.getId();
		}
		
		ContentValues values = new ContentValues();
		values.put(SqlDBHelper.USER_USERID, user.getId());
		values.put(SqlDBHelper.USER_FIRSTNAME, user.getFirstName());
		values.put(SqlDBHelper.USER_LASTNAME, user.getLastName());
		values.put(SqlDBHelper.USER_CONTACT, user.getContact());
		values.put(SqlDBHelper.USER_PASSWORD, user.getPassword());
		values.put(SqlDBHelper.USER_ISADMIN, user.getAdmin());
		
		localDB.insert(SqlDBHelper.TABLE_USERS, null, values);
		
		//userMemoryDB.put(user.getId(), user);
		
		return user.getId();
	}
	
	/**
	 * 
	 * @param item
	 *            Adds a New Item to the database if it doesn't already exist
	 * @return The items id number
	 */
	public int add(Item item) {
		
		Item foundExisting = queryItem(item.getId());
		if ( foundExisting != null && foundExisting.getId() != 0 ) {
			return foundExisting.getId();
		}
		
		// Create Item in Item Table
		ContentValues values = new ContentValues();
		values.put(SqlDBHelper.ITEM_ITEMID, item.getId());
		values.put(SqlDBHelper.ITEM_NAME, item.getName());
		
		if ( item.getType() instanceof FoundItem ) {
			values.put(SqlDBHelper.ITEM_TYPE, 0);
		} else if ( item.getType() instanceof LostItem ) {
			values.put(SqlDBHelper.ITEM_TYPE, 1);
		} else if ( item.getType() instanceof DonatedItem ) {
			values.put(SqlDBHelper.ITEM_TYPE, 2);
		} else {
			values.put(SqlDBHelper.ITEM_TYPE, 1);
		}
		Calendar currentDate = Calendar.getInstance();
		String date = "" + (currentDate.get(Calendar.YEAR)) + "-"
				+ currentDate.get(Calendar.MONTH) + "-"
				+ currentDate.get(Calendar.DAY_OF_MONTH);
		
		values.put(SqlDBHelper.ITEM_DESC, item.getDescription());
		values.put(SqlDBHelper.ITEM_IMAGE, toBytes(item.getImage()));
		values.putAll(parseItemAddress(item.getAddress()));
		values.put(SqlDBHelper.ITEM_CREATIONDATE, date);
		
		localDB.insert(SqlDBHelper.TABLE_ITEMS, null, values);
		
		// Create reference in ownership table
		values = new ContentValues();
		
		values.put(SqlDBHelper.USER_USERID, item.getOwnerId());
		values.put(SqlDBHelper.ITEM_ITEMID, item.getId());
		localDB.insert(SqlDBHelper.TABLE_USEROWNED, null, values);
		// Add owned item to list of owners item that exists in memory..
		
		
		// Populate Categories Table
		values = new ContentValues();
		for ( String desc : item.getCategories() ) {
			values.put(SqlDBHelper.ITEM_ITEMID, item.getId());
			values.put(SqlDBHelper.ITEMCATEGORY_CATEGORY, desc);
			localDB.insert(SqlDBHelper.TABLE_ITEMCATEGORIES, null, values);
		}
		
		// Keep object in memory for reference
		//itemMemoryDB.put(item.getId(), item);
		
		return item.getId();
	}
	
	/**
	 * 
	 * @param user
	 *            Removes this User Object from the Database
	 */
	public void remove(Item itemObj) {
		
		//itemMemoryDB.remove(itemObj.getId());
		localDB.delete(SqlDBHelper.TABLE_ITEMS, SqlDBHelper.ITEM_ITEMID + "=?",
				new String[] { "" + itemObj.getId() });
		localDB.delete(SqlDBHelper.TABLE_ITEMCATEGORIES,
				SqlDBHelper.ITEM_ITEMID + "=?",
				new String[] { "" + itemObj.getId() });
		localDB.delete(SqlDBHelper.TABLE_USEROWNED, SqlDBHelper.ITEM_ITEMID
				+ "=?", new String[] { "" + itemObj.getId() });
	}
	
	public void remove(User userObj) {
		for ( int foo : userObj.getItemsList() ) {
			remove(queryItem(foo));
		}
		
		//userMemoryDB.remove(userObj.getId());
		
		localDB.delete(SqlDBHelper.TABLE_USERS, SqlDBHelper.USER_USERID + "=?",
				new String[] { "" + userObj.getId() });
		localDB.delete(SqlDBHelper.TABLE_USEROWNED, SqlDBHelper.USER_USERID
				+ "=?", new String[] { "" + userObj.getId() });
		
	}
	
	/**
	 * Save Memory version of this Object to the Database
	 * 
	 * @param userObject
	 */
	public void save(User userObject) {
		User test = queryUser(userObject.getId());
		if ( !(test.getId() == userObject.getId()) ) {
			return;
		}
		
			ContentValues values = new ContentValues();
			values.put(SqlDBHelper.USER_USERID, userObject.getId());
			values.put(SqlDBHelper.USER_FIRSTNAME, userObject.getFirstName());
			values.put(SqlDBHelper.USER_LASTNAME, userObject.getLastName());
			values.put(SqlDBHelper.USER_CONTACT, userObject.getContact());
			values.put(SqlDBHelper.USER_PASSWORD, userObject.getPassword());
			values.put(SqlDBHelper.USER_ISADMIN, userObject.getAdmin());
			values.put(SqlDBHelper.USER_LOGINTRIES, userObject.getTryCount());
			values.put(SqlDBHelper.USER_ISLOCKED, userObject.getLockedStatus());
			localDB.update(SqlDBHelper.TABLE_USERS, values, SqlDBHelper.USER_USERID + "=?", new String[] { "" + userObject.getId() });
			
			if ( !userObject.getItemsList().isEmpty() ) {
				values = new ContentValues();
				for ( int _itemID : userObject.getItemsList() ) {
					values = new ContentValues();
					values.put(SqlDBHelper.USER_USERID, userObject.getId());
					values.put(SqlDBHelper.ITEM_ITEMID, _itemID);
				}
				
				localDB.update(SqlDBHelper.TABLE_USEROWNED, values, SqlDBHelper.USER_USERID + "=?", new String[] { "" + userObject.getId() });
			}
			
		}
	
	/**
	 * Save the Memory Version of this Object to the Database
	 * 
	 * @param itemObject
	 */
	public void save(Item itemObject) {
		
		Item test = queryItem(itemObject.getId());
		if ( !(test.getId() == itemObject.getId()) ) {
			return;
		}
		
		ContentValues values = new ContentValues();
		values.put(SqlDBHelper.ITEM_ITEMID, itemObject.getId());
		values.put(SqlDBHelper.ITEM_NAME, itemObject.getName());
		values.put(SqlDBHelper.ITEM_DESC, itemObject.getDescription());
		values.putAll(parseItemAddress(itemObject.getAddress()));
		values.put(SqlDBHelper.ITEM_CREATIONDATE, itemObject.getCreationDate());
		values.put(SqlDBHelper.ITEM_IMAGE, toBytes(itemObject.getImage()));
		
		ContentValues categories = new ContentValues();
		
		// Just delete all categories and recreate them in DB
		localDB.delete(SqlDBHelper.TABLE_ITEMCATEGORIES,
				SqlDBHelper.ITEM_ITEMID + "=?",
				new String[] { "" + itemObject.getId() });
		
		for ( String desc : itemObject.getCategories() ) {
			categories.put(SqlDBHelper.ITEM_ITEMID, itemObject.getId());
			categories.put(SqlDBHelper.ITEMCATEGORY_CATEGORY, desc);
			localDB.insert(SqlDBHelper.TABLE_ITEMCATEGORIES, null, categories);
		}
		
		if ( itemObject.getType() instanceof FoundItem ) {
			values.put(SqlDBHelper.ITEM_TYPE, 0);
		} else if ( itemObject.getType() instanceof LostItem ) {
			values.put(SqlDBHelper.ITEM_TYPE, 1);
		} else if ( itemObject.getType() instanceof DonatedItem ) {
			values.put(SqlDBHelper.ITEM_TYPE, 2);
		} else {
			values.put(SqlDBHelper.ITEM_TYPE, 1);
		}
		
		localDB.update(SqlDBHelper.TABLE_ITEMS, values, SqlDBHelper.ITEM_ITEMID
				+ "=?", new String[] { "" + itemObject.getId() });
		
		// Maybe the owner of the object Changed?!?!?
		values = new ContentValues();
		values.put(SqlDBHelper.USER_USERID, itemObject.getOwnerId());
		localDB.update(SqlDBHelper.TABLE_USEROWNED, values,
				SqlDBHelper.ITEM_ITEMID + "=?",
				new String[] { "" + itemObject.getId() });
		
		//itemMemoryDB.put(itemObject.getId(), itemObject);
		
	}
	
	// }
	
	/**
	 * Special Null Item Object
	 * 
	 * @return
	 */
	public static Item getNullItem() {
		return nullItem;
	}
	
	/**
	 * Special Null User Object
	 * 
	 * @return
	 */
	public static User getNullUser() {
		return nullUser;
	}
	
	private static ContentValues parseItemAddress(Address foo) {
		ContentValues result = new ContentValues();
		
		if ( foo != null ) {
			if ( foo.hasLongitude() ) {
				result.put(SqlDBHelper.ITEM_LOCLONG, foo.getLongitude());
			}
			if ( foo.hasLatitude() ) {
				result.put(SqlDBHelper.ITEM_LOCLAT, foo.getLatitude());
			}
			if ( foo.getMaxAddressLineIndex() > -1 ) {
				result.put(SqlDBHelper.ITEM_LOCADR0, foo.getAddressLine(0));
				result.put(SqlDBHelper.ITEM_LOCADR1, foo.getAddressLine(1));
			}
		}
		return result;
	}
	
	private static Address parseItemAddress(Cursor foo) {
		Address result = new Address(Locale.getDefault());
		if ( foo.getColumnIndex(SqlDBHelper.ITEM_LOCLONG) > -1 ) {
			result.setLongitude(foo.getDouble(foo
					.getColumnIndex(SqlDBHelper.ITEM_LOCLONG)));
			result.setLatitude(foo.getDouble(foo
					.getColumnIndex(SqlDBHelper.ITEM_LOCLAT)));
			result.setAddressLine(0,
					foo.getString(foo.getColumnIndex(SqlDBHelper.ITEM_LOCADR0)));
			result.setAddressLine(1,
					foo.getString(foo.getColumnIndex(SqlDBHelper.ITEM_LOCADR1)));
		}
		return result;
	}
	
	private class SqlDBHelper extends SQLiteOpenHelper {
		static final String	SQL_DBName				= "WMSDb";
		static final int	SQL_DBVersion			= 37;
		static final String	USER_USERID				= "UserID";
		static final String	USER_FIRSTNAME			= "firstName";
		static final String	USER_LASTNAME			= "lastName";
		static final String	USER_PASSWORD			= "password";
		static final String	USER_CONTACT			= "contact";
		static final String	USER_ISADMIN			= "isAdmin";
		static final String	USER_LOGINTRIES			= "loginTries";
		static final String	USER_ISLOCKED			= "isLocked";
		static final String	TABLE_USERS				= "Users";
		static final String	TABLE_USEROWNED			= "ItemsOwned";
		static final String	ITEM_ITEMID				= "itemID";
		static final String	TABLE_ITEMS				= "Items";
		static final String	TABLE_ITEMCATEGORIES	= "ItemCategories";
		static final String	ITEMCATEGORY_CATEGORY	= "categories";
		static final String	ITEM_NAME				= "name";
		static final String	ITEM_TYPE				= "itemType";
		static final String	ITEM_DESC				= "itemDescription";
		static final String	ITEM_IMAGE				= "image";
		static final String	ITEM_CREATIONDATE		= "creationDate";
		static final String	ITEM_LOCLONG			= "LocationLong";
		static final String	ITEM_LOCLAT				= "LocationLat";
		static final String	ITEM_LOCADR0			= "LocationAddressLine0";
		static final String	ITEM_LOCADR1			= "LocationAddressLine1";
		
		public SqlDBHelper(Context context) {
			super(context, SQL_DBName, null, SQL_DBVersion);
		}
		
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("DROP TABLE IF EXISTS Users;");
			db.execSQL("DROP TABLE IF EXISTS Items;");
			db.execSQL("DROP TABLE IF EXISTS ItemsOwned;");
			db.execSQL("DROP TABLE IF EXISTS ItemCategories;");
			// Cannot create items with add items every time...
			// add new item must also check for item in DB...
			
			String createUserTable = "CREATE TABLE " + TABLE_USERS + " ( "
					+ USER_USERID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
					+ USER_FIRSTNAME + " VARCHAR," + USER_LASTNAME
					+ " VARCHAR," + USER_PASSWORD + " VARCHAR NOT NULL,"
					+ USER_CONTACT + " VARCHAR UNIQUE NOT NULL," + USER_ISADMIN
					+ " BOOLEAN DEFAULT FALSE," + USER_LOGINTRIES
					+ " INTEGER DEFAULT 0," + USER_ISLOCKED
					+ " BOOLEAN DEFAULT FALSE );";
			String createItemsOwned = "CREATE TABLE " + TABLE_USEROWNED + " ( "
					+ USER_USERID + " INTEGER REFERENCES " + TABLE_USERS + "("
					+ USER_USERID + ") ON DELETE CASCADE, " + ITEM_ITEMID
					+ " INTEGER REFERENCES Items(" + ITEM_ITEMID
					+ ") ON DELETE CASCADE, " + "PRIMARY KEY (" + USER_USERID
					+ ", " + ITEM_ITEMID + "));";
			String createItemCategories = "CREATE TABLE "
					+ TABLE_ITEMCATEGORIES + " ( " 
					+ ITEM_ITEMID + " INTEGER REFERENCES Items(" 
					+ ITEM_ITEMID + ") ON DELETE CASCADE, "
					+ ITEMCATEGORY_CATEGORY	+ " VARCHAR NOT NULL," 
					+ "PRIMARY KEY(" + ITEM_ITEMID + ", " + ITEMCATEGORY_CATEGORY + "));";
			String createItemTable = "CREATE TABLE " + TABLE_ITEMS + " ( "
					+ ITEM_ITEMID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
					+ ITEM_NAME + " VARCHAR NOT NULL, " 
					+ ITEM_TYPE	+ " BLOB NOT NULL, " 
					+ ITEM_DESC + " VARCHAR, "
					+ ITEM_IMAGE + " BLOB, " 
					+ ITEM_LOCLONG + " FLOAT, "
					+ ITEM_LOCLAT + " FLOAT, " 
					+ ITEM_LOCADR0 + " VARCHAR, "
					+ ITEM_LOCADR1 + " VARCHAR, " 
					+ ITEM_CREATIONDATE
					+ " DATE NOT NULL);";
			db.execSQL(createUserTable);
			db.execSQL(createItemTable);
			db.execSQL(createItemCategories);
			db.execSQL(createItemsOwned);
			
		}
		
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			onCreate(db);
		}
		
		@Override
		public void onOpen(SQLiteDatabase db) {
			super.onOpen(db);
			if ( !db.isReadOnly() ) {
				// Enable foreign key constraints
				db.execSQL("PRAGMA foreign_keys=ON;");
			}
			
		}
	}
	
	private static Bitmap toBitmap(byte[] bytes) {
		if ( bytes != null) {
		InputStream input = new ByteArrayInputStream(bytes);
		
		Bitmap foo = null;
		foo = BitmapFactory.decodeStream(input);
		
		return foo;
		} else {
			return null;
		}
	}
	
	private static byte[] toBytes(Bitmap object) {
		if (object instanceof Bitmap) {	
		ByteArrayOutputStream bos = new ByteArrayOutputStream();
			object.compress(Bitmap.CompressFormat.PNG, 100, bos);
			 
			return bos.toByteArray();
		} else {
			return null;
		}
	}
}
